﻿using System;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using log4net;

namespace IntelligentStorWpfApp.Helper
{
    public static class DBHelper
    {
        private static MySqlConnection connection;
        private static readonly ILog log = LogManager.GetLogger(typeof(DBHelper));
        /// <summary>
        /// 获取Connection对象
        /// </summary>
        private static MySqlConnection Connection
        {
            get
            {
                try
                {
                    //读取配置文件
                    string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString.ToString();
                    if (connection == null)
                    {
                        connection = new MySqlConnection(connectionString);
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                    return connection;
                }
                catch (Exception e)
                {
                    log.Error(e);
                    return null;
                }
            }
        }

        /// <summary>
        /// 获取Command对象
        /// </summary>
        /// <param name="sql">[SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="mysqlParameter">参数可为null</param>
        /// <returns>返回创建好的Command对象</returns>
        private static MySqlCommand ExecuteCommand(string sql, CommandType commandType, MySqlParameter[] mysqlParameter)
        {
            try
            {
                using (MySqlCommand sqlCommand = new MySqlCommand(sql, Connection))
                {
                    if (mysqlParameter != null && mysqlParameter.Length > 0)
                    {
                        sqlCommand.Parameters.AddRange(mysqlParameter);
                    }
                    sqlCommand.CommandType = commandType;
                    return sqlCommand;
                }
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return null;
            }
        }

        /// <summary>
        /// 执行更新操作：增/删/改
        /// </summary>
        /// <param name="sql">[增/删/改SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="mysqlParameter">参数可为null</param>
        /// <returns>返回true 则更新成功 否则 更新失败</returns>
        public static bool ExecuteNonQuery(string sql, MySqlParameter[] mysqlParameter)
        {
            try
            {
                MySqlCommand sqlCommand = ExecuteCommand(sql, CommandType.Text, mysqlParameter);
                int result = sqlCommand.ExecuteNonQuery();
                return result != 0 ? true : false;
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 执行登录操作
        /// </summary>
        /// <param name="sql">[查询SQL语句-select count(*)...] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="mysqlParameter">参数可为null</param>
        /// <returns>返回true 则登录成功 否则 登录失败</returns>
        public static bool ExecuteScalar(string sql, MySqlParameter[] mysqlParameter)
        {
            try
            {
                MySqlCommand sqlCommand = ExecuteCommand(sql, CommandType.Text, mysqlParameter);
                int result = Convert.ToInt32(sqlCommand.ExecuteScalar());
                return result != 0 ? true : false;
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 获取DataReader对象
        /// </summary>
        /// <param name="sql">[查询SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="mysqlParameter">参数可为null</param>
        /// <returns>返回满足条件的DataReader对象</returns>
        public static MySqlDataReader ExecuteReader(string sql, MySqlParameter[] mysqlParameter)
        {
            try
            {
                MySqlCommand sqlCommand = ExecuteCommand(sql, CommandType.Text, mysqlParameter);
                MySqlDataReader reader = sqlCommand.ExecuteReader();
                return reader;
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return null;
            }
        }

        /// <summary>
        /// 获取DataTable对象
        /// </summary>
        /// <param name="sql">[查询SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="mysqlParameter">参数可为null</param>
        /// <returns>返回满足条件的DataTable对象</returns>
        public static DataTable ExecuteDataSet(string sql, MySqlParameter[] mysqlParameter)
        {
            try
            {
                DataSet ds = new DataSet();
                MySqlCommand sqlCommand = ExecuteCommand(sql, CommandType.Text, mysqlParameter);
                MySqlDataAdapter da = new MySqlDataAdapter(sqlCommand);
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return null;
            }
        }
    }
}
